using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace ClientTools.Library.Versions
{
    public class VersionManager: BaseManager
    {
        public VersionManager(string connectionString)
            : base(connectionString)
        { }

        public VersionInfo GetVersionInfos()
        {
            VersionInfo returnValue = new VersionInfo();
            SqlConnection con = this.GetConnection();

            System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM cmsContent", con);
            returnValue.Content = (int)com.ExecuteScalar();

            com = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM cmsDocument", con);
            returnValue.Documents = (int)com.ExecuteScalar();

            com = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM cmsContentVersion", con);
            returnValue.Versions = (int)com.ExecuteScalar();

            com = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM cmsPropertyData", con);
            returnValue.PropertyData = (int)com.ExecuteScalar();

            return returnValue;
        }

        public VersionInfo DeleteVersionsByDate(DateTime date)
        {
            // initialize the returning object
            VersionInfo returnValue = new VersionInfo();

            // initialize and open the Connection
            System.Data.SqlClient.SqlConnection con = this.GetConnection();

            // first load all content elements
            DataSet dsContent = new DataSet();
            SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent", con);
            daContent.Fill(dsContent);

            // and all versions
            SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion", con);
            DataSet dsContentVersion = new DataSet();
            daContentVersion.Fill(dsContentVersion);

            // and all documents
            SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
            DataSet dsDocuments = new DataSet();
            daDocuments.Fill(dsDocuments);

            // for each content
            foreach (DataRow drContent in dsContent.Tables[0].Rows)
            {
                int nodeId = (int)drContent["nodeID"];
                // get the versions of this content element sorted descending by VersionDate 
                DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
                // set the variable if a published version is found
                bool bFoundFirstPublished = false;
                // for each version
                for (int i = 0; i < drContentVersion.Length; i++)
                {
                    Guid version = (Guid)drContentVersion[i]["versionID"];
                    // get the document of this version
                    DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

                    // if there is no document leave this version in and go on with the loop
                    if ((drDocuments == null) || drDocuments.Length == 0)
                    {
                        continue;
                    }
                    DataRow drDocument = drDocuments[0];

                    // if the published version isn't found yet
                    if (!bFoundFirstPublished)
                    {
                        // check if the actual version is published
                        if ((bool)drDocument["published"])
                        {
                            // if it is published leave this version in and set the variable that a published version is found
                            bFoundFirstPublished = true;
                        }
                        // and go on with the loop
                        continue;
                    }
                    // if we got here then check if the VersionDate is lower than the given date
                    if (((DateTime)drContentVersion[i]["VersionDate"] < date) && (!(bool)drDocument["published"]))
                    {
                        // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                        returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                        returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                        returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                    }
                }
                returnValue.Content++;
            }
            return returnValue;
        }

        public VersionInfo DeleteVersionsByNumberOfItems(int numberOfItems)
        {
            // initialize the returning object
            VersionInfo returnValue = new VersionInfo();

            // initialize and open the Connection
            System.Data.SqlClient.SqlConnection con = this.GetConnection();

            // first load all content elements
            DataSet dsContent = new DataSet();
            SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent", con);
            daContent.Fill(dsContent);

            // and all versions
            SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion", con);
            DataSet dsContentVersion = new DataSet();
            daContentVersion.Fill(dsContentVersion);

            // and all documents
            SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
            DataSet dsDocuments = new DataSet();
            daDocuments.Fill(dsDocuments);

            // for each content
            foreach (DataRow drContent in dsContent.Tables[0].Rows)
            {
                int nodeId = (int)drContent["nodeID"];
                int versionsLeave = 0;
                // get the versions of this content element sorted descending by VersionDate 
                DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
                // set the variable if a published version is found
                bool bFoundFirstPublished = false;
                // for each version
                for (int i = 0; i < drContentVersion.Length; i++)
                {
                    Guid version = (Guid)drContentVersion[i]["versionID"];
                    // get the document of this version
                    DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

                    // if there is no document leave this version in and go on with the loop
                    if ((drDocuments == null) || drDocuments.Length == 0)
                    {
                        continue;
                    }
                    DataRow drDocument = drDocuments[0];

                    // if the published version isn't found yet
                    if (!bFoundFirstPublished)
                    {
                        // check if the actual version is published
                        if ((bool)drDocument["published"])
                        {
                            // if it is published leave this version in and set the variable that a published version is found
                            bFoundFirstPublished = true;
                            versionsLeave++;
                        }
                        // and go on with the loop
                        continue;
                    }
                    // if we got here then check if the number of items to leave is reached
                    if ((versionsLeave > numberOfItems) && (!(bool)drDocument["published"]))
                    {
                        // if so then delete all property data, the document and the version itself and increment the counters for the return infos
                        returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                        returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                        returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
                    }
                    // increment the version counter
                    versionsLeave++;
                }
                returnValue.Content++;
            }
            return returnValue;
        }

        public ContentNodeCollection GetNodes(int parentId, bool recursive)
        {
 
        }
    }
}
